<!DOCTYPE html>
<html lang="en" color-mode="light">

  <head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1" />
  <meta name="keywords" content="JavaScript,Hexo" />
  <meta name="author" content="超能虾米" />
  <meta name="description" content="" />
  
  
  <title>
    
      SQL走了索引还是很慢？ 
      
      
      |
    
     超能虾米的博客
  </title>

  
    <link rel="apple-touch-icon" href="/images/favicon.png">
    <link rel="icon" href="/images/favicon.png">
  

  <!-- Raleway-Font -->
  <link href="https://fonts.googleapis.com/css?family=Raleway&display=swap" rel="stylesheet">

  <!-- hexo site css -->
  <link rel="stylesheet" href="/css/main.css" />
  <link rel="stylesheet" href="//at.alicdn.com/t/font_1886449_67xjft27j1l.css" />
  <!-- 代码块风格 -->
  

  <!-- jquery3.3.1 -->
  
    <script defer type="text/javascript" src="/plugins/jquery.min.js"></script>
  

  <!-- fancybox -->
  
    <link href="/plugins/jquery.fancybox.min.css" rel="stylesheet">
    <script defer type="text/javascript" src="/plugins/jquery.fancybox.min.js"></script>
  
  
<script src="/js/fancybox.js"></script>


  

  

  <script>
    var html = document.documentElement
    const colorMode = localStorage.getItem('color-mode')
    if (colorMode) {
      document.documentElement.setAttribute('color-mode', colorMode)
    }
  </script>
<meta name="generator" content="Hexo 5.4.2"><link rel="alternate" href="/atom.xml" title="超能虾米的博客" type="application/atom+xml">
</head>


  <body>
    <div id="app">
      <div class="header">
  <div class="avatar">
    <a href="/">
      <!-- 头像取消懒加载，添加no-lazy -->
      
        <img src="/images/头像.JPEG" alt="">
      
    </a>
    <div class="nickname"><a href="/">超能虾米</a></div>
  </div>
  <div class="navbar">
    <ul>
      
        <li class="nav-item" data-path="/">
          <a href="/">主页</a>
        </li>
      
        <li class="nav-item" data-path="/archives/">
          <a href="/archives/">归档</a>
        </li>
      
        <li class="nav-item" data-path="/categories/">
          <a href="/categories/">分类</a>
        </li>
      
        <li class="nav-item" data-path="/tags/">
          <a href="/tags/">标签</a>
        </li>
      
        <li class="nav-item" data-path="/friends/">
          <a href="/friends/">朋友</a>
        </li>
      
        <li class="nav-item" data-path="/about/">
          <a href="/about/">关于我</a>
        </li>
      
    </ul>
  </div>
</div>


<script src="/js/activeNav.js"></script>



      <div class="flex-container">
        <!-- 文章详情页，展示文章具体内容，url形式：https://yoursite/文章标题/ -->
<!-- 同时为「标签tag」，「朋友friend」，「分类categories」，「关于about」页面的承载页面，具体展示取决于page.type -->


  <!-- LaTex Display -->

  
    <script async type="text/javascript" src="/plugins/mathjax/tex-chtml.js"></script>
  
  <script>
    MathJax = {
      tex: {
        inlineMath: [['$', '$'], ['\\(', '\\)']]
      }
    }
  </script>





  <!-- clipboard -->

  
    <script async type="text/javascript" src="/plugins/clipboard.min.js"></script>
  
  
<script src="/js/codeCopy.js"></script>







  

  

  

  
  <!-- 文章内容页 url形式：https://yoursite/文章标题/ -->
  <div class="container post-details" id="post-details">
    <div class="post-content">
      <div class="post-title">SQL走了索引还是很慢？</div>
      <div class="post-attach">
        <span class="post-pubtime">
          <i class="iconfont icon-updatetime mr-10" title="Update time"></i>
          2023-05-11 17:40:03
        </span>
        
              <span class="post-categories">
                <i class="iconfont icon-bookmark" title="Categories"></i>
                
                <span class="span--category">
                  <a href="/categories/%E5%90%8E%E7%AB%AF/" title="后端">
                    <b>#</b> 后端
                  </a>
                </span>
                
                <span class="span--category">
                  <a href="/categories/%E5%90%8E%E7%AB%AF/%E6%95%B0%E6%8D%AE%E5%BA%93/" title="数据库">
                    <b>#</b> 数据库
                  </a>
                </span>
                
              </span>
          
      </div>
      <div class="markdown-body">
        <h2 id="查询速度影响因素"><a href="#查询速度影响因素" class="headerlink" title="查询速度影响因素"></a>查询速度影响因素</h2><p>MySQL 用到了索引和执行时间的长短没有必然关系，确定查询执行效率的是 “<strong>扫描行数</strong>”与“<strong>回表次数</strong>”。</p>
<ul>
<li>扫描行数</li>
<li>回表次数<br>在实际的 sql 优化过程中，也是尽量去优化这两块影响因素。</li>
</ul>
<h2 id="MySQL-选择-B-树索引结构的原因"><a href="#MySQL-选择-B-树索引结构的原因" class="headerlink" title="MySQL 选择 B+树索引结构的原因"></a>MySQL 选择 B+树索引结构的原因</h2><ol>
<li>内存占用：B+树可以把所有数据都放在叶子节点上，内部节点只存储索引信息，因此可以减少内存的占用。</li>
<li>顺序访问：B+树中的叶子节点之间通过链表相连，可以很方便地进行顺序访问和范围查询。</li>
<li>磁盘访问：对于磁盘上每个数据块大小固定的情况，B+树可以使一个节点大小等于一个数据块大小，这样可以最大化地利用磁盘容量。</li>
<li>多级索引：B+树支持多级索引，可以在数据量较大时不断分裂增加层数，提高查询效率。</li>
</ol>
<h2 id="聚集索引和非聚集索引（普通索引）"><a href="#聚集索引和非聚集索引（普通索引）" class="headerlink" title="聚集索引和非聚集索引（普通索引）"></a>聚集索引和非聚集索引（普通索引）</h2><ul>
<li>聚簇索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能有一个聚簇索引。<strong>叶子节点存储索引和行记录，聚簇索引查询会很快，因为可以直接定位到行记录</strong></li>
<li>非聚簇索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。<strong>叶子节点存储聚簇索引值（主键id），需要扫码两遍索引树，先通过普通索引定位到主键值id，再通过聚集索引定位到行记录</strong></li>
</ul>
<p>InnoDB 必须有一个主键索引，这里给 id 为主键，其次有 a、b 两个字段，再给 a 字段（姓名字段）一个普通索引，目前就有了主键索引和 a 索引树结构，如下图所示：<br><img src="http://qiniu.c77544s.top/picgo/202305111746033.png" alt="image.png"></p>
<blockquote>
<p>InnoDB 默认有主键索引，存储的是主键 id 1、2、3…，它是采用 B+ tree 的聚簇索引，所有的数据都存放在叶子结点中，而普通索引 a 采用非聚集索引，数据只包含对应的 id，没有整行数据，所以如果通过普通索引找到了某个 id，需要 select 别的字段，则还需要回表找主键索引中的整行数据取值！</p>
</blockquote>
<h2 id="几种调优方式"><a href="#几种调优方式" class="headerlink" title="几种调优方式"></a>几种调优方式</h2><h3 id="索引覆盖"><a href="#索引覆盖" class="headerlink" title="索引覆盖"></a>索引覆盖</h3><p>索引覆盖，即将查询 sql 中的字段添加到联合索引里面，只要保证查询语句里面的字段都在索引文件中，就无需进行回表查询；</p>
<h3 id="索引下推（系统优化）"><a href="#索引下推（系统优化）" class="headerlink" title="索引下推（系统优化）"></a>索引下推（系统优化）</h3><p>在 MySQL5.6的版本中推出，用于优化查询。<strong>在索引遍历的过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数</strong>。</p>
<h3 id="自查询（优化分页）"><a href="#自查询（优化分页）" class="headerlink" title="自查询（优化分页）"></a>自查询（优化分页）</h3><p>优化<strong>超多分页的场景</strong>。查询条件放到子查询中，子查询只查主键，然后使用子查询中确认的主键关联其他属性字段。</p>
<h3 id="数据压缩"><a href="#数据压缩" class="headerlink" title="数据压缩"></a>数据压缩</h3><p>比如要插入的很多连续的数据值都一样，则可以压缩成一条数据插入。</p>
<h3 id="硬件调优"><a href="#硬件调优" class="headerlink" title="硬件调优"></a>硬件调优</h3><p>增大 innodb buffer pool 多利用内存,减少硬盘回表</p>

      </div>
      
        <div class="prev-or-next">
          <div class="post-foot-next">
            
              <a href="/2023/05/11/%E5%90%8E%E7%AB%AF/Spring%20Cloud/%E6%8E%A5%E5%8F%A3%E5%B9%82%E7%AD%89/" target="_self">
                <i class="iconfont icon-chevronleft"></i>
                <span>Prev</span>
              </a>
            
          </div>
          <div class="post-attach">
            <span class="post-pubtime">
              <i class="iconfont icon-updatetime mr-10" title="Update time"></i>
              2023-05-11 17:40:03
            </span>
            
                  <span class="post-categories">
                    <i class="iconfont icon-bookmark" title="Categories"></i>
                    
                    <span class="span--category">
                      <a href="/categories/%E5%90%8E%E7%AB%AF/" title="后端">
                        <b>#</b> 后端
                      </a>
                    </span>
                    
                    <span class="span--category">
                      <a href="/categories/%E5%90%8E%E7%AB%AF/%E6%95%B0%E6%8D%AE%E5%BA%93/" title="数据库">
                        <b>#</b> 数据库
                      </a>
                    </span>
                    
                  </span>
              
          </div>
          <div class="post-foot-prev">
            
              <a href="/2023/05/11/%E5%90%8E%E7%AB%AF/%E4%B8%AD%E9%97%B4%E4%BB%B6/ES%E4%B8%AD%E7%9A%84%E5%80%92%E6%8E%92%E7%B4%A2%E5%BC%95/" target="_self">
                <span>Next</span>
                <i class="iconfont icon-chevronright"></i>
              </a>
            
          </div>
        </div>
      
    </div>
    
  <div id="btn-catalog" class="btn-catalog">
    <i class="iconfont icon-catalog"></i>
  </div>
  <div class="post-catalog hidden" id="catalog">
    <div class="title">Contents</div>
    <div class="catalog-content">
      
        <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E9%80%9F%E5%BA%A6%E5%BD%B1%E5%93%8D%E5%9B%A0%E7%B4%A0"><span class="toc-text">查询速度影响因素</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#MySQL-%E9%80%89%E6%8B%A9-B-%E6%A0%91%E7%B4%A2%E5%BC%95%E7%BB%93%E6%9E%84%E7%9A%84%E5%8E%9F%E5%9B%A0"><span class="toc-text">MySQL 选择 B+树索引结构的原因</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%81%9A%E9%9B%86%E7%B4%A2%E5%BC%95%E5%92%8C%E9%9D%9E%E8%81%9A%E9%9B%86%E7%B4%A2%E5%BC%95%EF%BC%88%E6%99%AE%E9%80%9A%E7%B4%A2%E5%BC%95%EF%BC%89"><span class="toc-text">聚集索引和非聚集索引（普通索引）</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%87%A0%E7%A7%8D%E8%B0%83%E4%BC%98%E6%96%B9%E5%BC%8F"><span class="toc-text">几种调优方式</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E8%A6%86%E7%9B%96"><span class="toc-text">索引覆盖</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%B4%A2%E5%BC%95%E4%B8%8B%E6%8E%A8%EF%BC%88%E7%B3%BB%E7%BB%9F%E4%BC%98%E5%8C%96%EF%BC%89"><span class="toc-text">索引下推（系统优化）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%87%AA%E6%9F%A5%E8%AF%A2%EF%BC%88%E4%BC%98%E5%8C%96%E5%88%86%E9%A1%B5%EF%BC%89"><span class="toc-text">自查询（优化分页）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%8E%8B%E7%BC%A9"><span class="toc-text">数据压缩</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%A1%AC%E4%BB%B6%E8%B0%83%E4%BC%98"><span class="toc-text">硬件调优</span></a></li></ol></li></ol>
      
    </div>
  </div>

  
<script src="/js/catalog.js"></script>




    
      <div class="comments-container">
        




  
    <script async type="text/javascript" src="/plugins/valine.min.js" onload="loadValineSuc(this)"></script>
  

  <div id="vcomments"></div>

  <script>
    function loadValineSuc() {
      new Valine({
        el: '#vcomments',
        appId: 'H2QqshKF8elT2Si5bfKynT9h-9Nh9j0Va',
        appKey: 'WgtVppOWkTZ8fWNReNI1Ymf7',
        placeholder: '有问题请留言吧!',
        avatar: 'retro',
        lang: 'en'
      })
    }
  </script>

    <style>
      .comments-container .v .vempty {
        display: none!important;
      }
    </style>




      </div>
    
  </div>


        
<div class="footer">
  <div class="social">
    <ul>
      
        <li>
          <a title="github" target="_blank" rel="noopener" href="https://github.com/zchengsite/hexo-theme-oranges">
            <i class="iconfont icon-github"></i>
          </a>
        </li>
      
        <li>
          <a title="email" target="_blank" rel="noopener" href="https://c77544s.github.io/about/">
            <i class="iconfont icon-envelope"></i>
          </a>
        </li>
      
        <li>
          <a title="wechat" target="_blank" rel="noopener" href="https://c77544s.github.io/about/">
            <i class="iconfont icon-wechat"></i>
          </a>
        </li>
      
        <li>
          <a title="rss" href="/atom.xml">
            <i class="iconfont icon-rss"></i>
          </a>
        </li>
      
    </ul>
  </div>
  
    
    <div class="footer-more">
      
        <a target="_blank" rel="noopener" href="https://github.com/zchengsite/hexo-theme-oranges">Copyright © 2023 Oranges</a>
        
    </div>
  
    
    <div class="footer-more">
      
        <a target="_blank" rel="noopener" href="https://github.com/zchengsite/hexo-theme-oranges">Theme by Oranges | Powered by Hexo</a>
        
    </div>
  
  
</div>

      </div>

      <div class="tools-bar">
        <div class="back-to-top tools-bar-item hidden">
  <a href="javascript: void(0)">
    <i class="iconfont icon-chevronup"></i>
  </a>
</div>


<script src="/js/backtotop.js"></script>



        
  <div class="search-icon tools-bar-item" id="search-icon">
    <a href="javascript: void(0)">
      <i class="iconfont icon-search"></i>
    </a>
  </div>

  <div class="search-overlay hidden">
    <div class="search-content" tabindex="0">
      <div class="search-title">
        <span class="search-icon-input">
          <a href="javascript: void(0)">
            <i class="iconfont icon-search"></i>
          </a>
        </span>
        
          <input type="text" class="search-input" id="search-input" placeholder="搜索...">
        
        <span class="search-close-icon" id="search-close-icon">
          <a href="javascript: void(0)">
            <i class="iconfont icon-close"></i>
          </a>
        </span>
      </div>
      <div class="search-result" id="search-result"></div>
    </div>
  </div>

  <script type="text/javascript">
    var inputArea = document.querySelector("#search-input")
    var searchOverlayArea = document.querySelector(".search-overlay")

    inputArea.onclick = function() {
      getSearchFile()
      this.onclick = null
    }

    inputArea.onkeydown = function() {
      if(event.keyCode == 13)
        return false
    }

    function openOrHideSearchContent() {
      let isHidden = searchOverlayArea.classList.contains('hidden')
      if (isHidden) {
        searchOverlayArea.classList.remove('hidden')
        document.body.classList.add('hidden')
        // inputArea.focus()
      } else {
        searchOverlayArea.classList.add('hidden')
        document.body.classList.remove('hidden')
      }
    }

    function blurSearchContent(e) {
      if (e.target === searchOverlayArea) {
        openOrHideSearchContent()
      }
    }

    document.querySelector("#search-icon").addEventListener("click", openOrHideSearchContent, false)
    document.querySelector("#search-close-icon").addEventListener("click", openOrHideSearchContent, false)
    searchOverlayArea.addEventListener("click", blurSearchContent, false)

    var searchFunc = function (path, search_id, content_id) {
      'use strict';
      var $input = document.getElementById(search_id);
      var $resultContent = document.getElementById(content_id);
      $resultContent.innerHTML = "<ul><span class='local-search-empty'>First search, index file loading, please wait...<span></ul>";
      $.ajax({
        // 0x01. load xml file
        url: path,
        dataType: "xml",
        success: function (xmlResponse) {
          // 0x02. parse xml file
          var datas = $("entry", xmlResponse).map(function () {
            return {
              title: $("title", this).text(),
              content: $("content", this).text(),
              url: $("url", this).text()
            };
          }).get();
          $resultContent.innerHTML = "";

          $input.addEventListener('input', function () {
            // 0x03. parse query to keywords list
            var str = '<ul class=\"search-result-list\">';
            var keywords = this.value.trim().toLowerCase().split(/[\s\-]+/);
            $resultContent.innerHTML = "";
            if (this.value.trim().length <= 0) {
              return;
            }
            // 0x04. perform local searching
            datas.forEach(function (data) {
              var isMatch = true;
              var content_index = [];
              if (!data.title || data.title.trim() === '') {
                data.title = "Untitled";
              }
              var orig_data_title = data.title.trim();
              var data_title = orig_data_title.toLowerCase();
              var orig_data_content = data.content.trim().replace(/<[^>]+>/g, "");
              var data_content = orig_data_content.toLowerCase();
              var data_url = data.url;
              var index_title = -1;
              var index_content = -1;
              var first_occur = -1;
              // only match artiles with not empty contents
              if (data_content !== '') {
                keywords.forEach(function (keyword, i) {
                  index_title = data_title.indexOf(keyword);
                  index_content = data_content.indexOf(keyword);

                  if (index_title < 0 && index_content < 0) {
                    isMatch = false;
                  } else {
                    if (index_content < 0) {
                      index_content = 0;
                    }
                    if (i == 0) {
                      first_occur = index_content;
                    }
                    // content_index.push({index_content:index_content, keyword_len:keyword_len});
                  }
                });
              } else {
                isMatch = false;
              }
              // 0x05. show search results
              if (isMatch) {
                str += "<li><a href='" + data_url + "' class='search-result-title'>" + orig_data_title + "</a>";
                var content = orig_data_content;
                if (first_occur >= 0) {
                  // cut out 100 characters
                  var start = first_occur - 20;
                  var end = first_occur + 80;

                  if (start < 0) {
                    start = 0;
                  }

                  if (start == 0) {
                    end = 100;
                  }

                  if (end > content.length) {
                    end = content.length;
                  }

                  var match_content = content.substr(start, end);

                  // highlight all keywords
                  keywords.forEach(function (keyword) {
                    var regS = new RegExp(keyword, "gi");
                    match_content = match_content.replace(regS, "<span class=\"search-keyword\">" + keyword + "</span>");
                  });

                  str += "<p class=\"search-result-abstract\">" + match_content + "...</p>"
                }
                str += "</li>";
              }
            });
            str += "</ul>";
            if (str.indexOf('<li>') === -1) {
              return $resultContent.innerHTML = "<ul><span class='local-search-empty'>No result<span></ul>";
            }
            $resultContent.innerHTML = str;
          });
        },
        error: function(xhr, status, error) {
          $resultContent.innerHTML = ""
          if (xhr.status === 404) {
            $resultContent.innerHTML = "<ul><span class='local-search-empty'>The search.xml file was not found, please refer to：<a href='https://github.com/zchengsite/hexo-theme-oranges#configuration' target='_black'>configuration</a><span></ul>";
          } else {
            $resultContent.innerHTML = "<ul><span class='local-search-empty'>The request failed, Try to refresh the page or try again later.<span></ul>";
          }
        }
      });
      $(document).on('click', '#search-close-icon', function() {
        $('#search-input').val('');
        $('#search-result').html('');
      });
    }

    var getSearchFile = function() {
        var path = "/search.xml";
        searchFunc(path, 'search-input', 'search-result');
    }
  </script>




        
  <div class="tools-bar-item theme-icon" id="switch-color-scheme">
    <a href="javascript: void(0)">
      <i id="theme-icon" class="iconfont icon-moon"></i>
    </a>
  </div>

  
<script src="/js/colorscheme.js"></script>





        
  
    <div class="share-icon tools-bar-item">
      <a href="javascript: void(0)" id="share-icon">
        <i class="iconfont iconshare"></i>
      </a>
      <div class="share-content hidden">
        
          <a class="share-item" href="https://twitter.com/intent/tweet?text=' + SQL%E8%B5%B0%E4%BA%86%E7%B4%A2%E5%BC%95%E8%BF%98%E6%98%AF%E5%BE%88%E6%85%A2%EF%BC%9F + '&url=' + http%3A%2F%2Fc77544s.gitee.io%2F2023%2F05%2F11%2F%25E5%2590%258E%25E7%25AB%25AF%2F%25E6%2595%25B0%25E6%258D%25AE%25E5%25BA%2593%2FSQL%25E8%25B5%25B0%25E4%25BA%2586%25E7%25B4%25A2%25E5%25BC%2595%25E8%25BF%2598%25E6%2598%25AF%25E5%25BE%2588%25E6%2585%25A2%25EF%25BC%259F%2F + '" target="_blank" title="Twitter">
            <i class="iconfont icon-twitter"></i>
          </a>
        
        
          <a class="share-item" href="https://www.facebook.com/sharer.php?u=http://c77544s.gitee.io/2023/05/11/%E5%90%8E%E7%AB%AF/%E6%95%B0%E6%8D%AE%E5%BA%93/SQL%E8%B5%B0%E4%BA%86%E7%B4%A2%E5%BC%95%E8%BF%98%E6%98%AF%E5%BE%88%E6%85%A2%EF%BC%9F/" target="_blank" title="Facebook">
            <i class="iconfont icon-facebooksquare"></i>
          </a>
        
      </div>
    </div>
  
  
<script src="/js/shares.js"></script>



      </div>
    </div>
  </body>
</html>
